package com.eduardcapell.lopodder.db;

import java.net.URL;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.eduardcapell.lopodder.beans.BasicID3Tag;
import com.eduardcapell.lopodder.beans.Feed;
import com.eduardcapell.lopodder.beans.FeedElement;
import com.eduardcapell.lopodder.beans.FeedStatus;
import com.eduardcapell.lopodder.misc.Constants;

public class DBAccess {


	private DBAccess() {}

	public static final Connection connect() throws Exception {
		Connection conn = null;
		Class.forName(Constants.JDBC_DRIVER);
		conn = DriverManager.getConnection("jdbc:hsqldb:file:" + Constants.DB_LOCATION, "sa", "");

		System.out.println("Connection OK: " + conn.toString());
		return conn;
	}
	
	public static final void shutdown() throws Exception {
		String sql = "SHUTDOWN";
		Connection conn = connect();
		conn.createStatement().execute(sql);
		conn.close();
		conn = null;
		System.out.println("Shutdown OK.");
	}


	public static final void closeConnection(Connection conn) throws Exception {
		conn.close();
		conn = null;
		System.out.println("Connection closed.");
	}


	public static final void createDB() {
		String sql = "CREATE TABLE feed (id INT IDENTITY PRIMARY KEY, url VARCHAR(200), " + 
		"name VARCHAR(200), folder VARCHAR(500), id3_tags_apply BOOLEAN, " + 
		"id3_artist VARCHAR(200), check_interval INT, rename_file BOOLEAN, " +
        "status INT DEFAULT 0)";
		executeUpdate(sql);
		System.out.println("Table 'feed' created ok.");

		sql = "CREATE TABLE element (id INT IDENTITY PRIMARY KEY, " + 
		"id_feed INT, " + 
		"name VARCHAR(200), pubdate DATETIME, size INT, description VARCHAR(5000), " + 
		"url VARCHAR(400), content_type VARCHAR(50), downloaded BOOLEAN, " +
        "POS_PLAY INT DEFAULT 0, FILENAME VARCHAR(1000), " +
		"CONSTRAINT FK_1 FOREIGN KEY (id_feed) REFERENCES feed(id))";
		DBAccess.executeUpdate(sql);
		System.out.println("Table 'element' created ok.");

//		sql = "CREATE TABLE setting (id INT IDENTITY PRIMARY KEY, " + 
//		"value VARCHAR(100))";
//		DBAccess.executeUpdate(sql);
//		
//		sql = "CREATE TABLE feed_settings (id_feed INT NOT NULL, " + 
//		"id_setting INT NOT NULL, value VARCHAR(100), " + 
//		"PRIMARY KEY (id_feed, id_setting), " + 
//		"CONSTRAINT FK_2 FOREIGN KEY (id_feed) REFERENCES feed(id), " + 
//		"CONSTRAINT FK_3 FOREIGN KEY (id_setting) REFERENCES setting(id))";
//		DBAccess.executeUpdate(sql);
		
		try {
			DBAccess.shutdown();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static final void dropTables() {
		String sql = "DROP TABLE element";
		executeUpdate(sql);
		
		sql = "DROP TABLE setting";
		executeUpdate(sql);
		
		sql = "DROP TABLE feed_settings";
		executeUpdate(sql);
		
		sql = "DROP TABLE feed";
		executeUpdate(sql);
		
		try {
			DBAccess.shutdown();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static final void addFeed(Feed f) {
		Connection c = null;

		try {
			c = connect();
			PreparedStatement pst = c.prepareStatement("INSERT INTO feed (url, name, folder) " + 
			"VALUES (?, ?, ?)");
			pst.setString(1, f.getUrl().toString());
			pst.setString(2, f.getName());
			pst.setString(3, f.getFolder());
			pst.execute();
			
			f.setId(lastInsertId(c));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


	public static List<Feed> getAllFeeds() {
		List<Feed> feeds = new ArrayList<Feed>();
		Connection c = null;

		try {
			c = connect();
			String sql = "SELECT * FROM feed";
			ResultSet rs = c.createStatement().executeQuery(sql);

			if (rs != null) {
				while (rs.next()) {
					Feed f = new Feed(rs.getInt("id"), rs.getString("name"), 
						rs.getString("url"), rs.getString("folder"));
//					f.setApplyID3Tags(rs.getBoolean("id3_tags_apply"));
//					f.setCheckInterval(rs.getInt("check_interval"));
//					f.setRenameFileTo(rs.getString("rename_file"));
					loadElements(f, c);
					f.setLoadedElements(true);
					
					BasicID3Tag id3 = new BasicID3Tag();
//					id3.setAlbum(rs.getString("id3_album"));
//					id3.setArtist(rs.getString("id3_artist"));
//					id3.setSongTitle(rs.getString("id3_title"));
					
					f.setId3(id3);
					FeedStatus status = new FeedStatus(rs.getInt("status"));
                    f.setStatus(status);
					feeds.add(f);
				}
			}

			rs = c.createStatement().executeQuery(sql);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return feeds;
	}

	public static void deleteFeed(Feed f) {
		Connection c = null;
		String sql = null;
		PreparedStatement pst = null;
		
		try {
			c = connect();
			
			
			sql = "DELETE FROM element WHERE id_feed = ?";
			pst = c.prepareStatement(sql);
			pst.setInt(1, f.getId());
			pst.execute();
			
			sql = "DELETE FROM feed WHERE ID = ?";
			pst = c.prepareStatement(sql);
			pst.setInt(1, f.getId());
			pst.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

	}


	public static final void executeUpdate(String sql) {
		Connection c = null;

		try {
			c = connect();
			c.createStatement().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


	private static final void loadElements(Feed f, Connection c) {
		List<FeedElement> elements = new ArrayList<FeedElement>();

		try {
//			System.out.println("Connected OK");
			String sql = "SELECT * FROM element WHERE id_feed = ?";
			PreparedStatement pst = c.prepareStatement(sql);
			pst.setInt(1, f.getId());
			ResultSet rs = pst.executeQuery();

			if (rs != null) {
				while (rs.next()) {
					FeedElement fe = new FeedElement();
					fe.setContenType(rs.getString("content_type"));
					fe.setDate(rs.getDate("pubdate"));
					fe.setDescription(rs.getString("description"));
					fe.setDownloaded(rs.getBoolean("downloaded"));
					fe.setId(rs.getInt("id"));
					fe.setName(rs.getString("name"));
					fe.setSize(rs.getLong("size"));
					fe.setUrl(new URL(rs.getString("url")));
					fe.setFeed(f);
                    fe.setPosition(rs.getInt("pos_play"));
                    fe.setFileName(rs.getString("filename"));
					elements.add(fe);
				}
			}

//			System.out.println("Found " + elements.size() + " elements for Feed " + f.toString());

		} catch (Exception e) {
			e.printStackTrace();
		}

		f.setElements(elements);

	}

	public static final void loadElements(Feed f) {
		Connection c = null;

		try {
			c = connect();
			loadElements(f, c);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	/**
	 * Updates status "downloaded" for a feed element.
	 * @param fe
	 */
	public static void updateFeedElement(FeedElement fe) {
		Connection c = null;

		try {
			c = connect();
			String sql = "UPDATE element " + 
			"SET downloaded = ? WHERE id = ?";
			PreparedStatement pst = c.prepareStatement(sql);
			pst.setBoolean(1, fe.isDownloaded());
			pst.setInt(2, fe.getId());
			pst.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


    public static void updateFeedElementFileName(FeedElement fe) {
        Connection c = null;

		try {
			c = connect();
            String sql = "UPDATE element SET filename = ? " +
                "WHERE id = ?";
			PreparedStatement pst = c.prepareStatement(sql);
			pst.setString(1, fe.getFileName());
			pst.setInt(2, fe.getId());
			pst.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
    }
	
	
	/**
	 * Updates status "downloaded" for a feed element.
	 * @param fe
	 */
	public static void updateFeed(Feed f) {
		Connection c = null;

		try {
			c = connect();
			String sql = "UPDATE feed " + 
			"SET url = ?, name = ?, folder = ?, id3_tags_apply = ?, check_interval = ?, " + 
			"rename_file = ? WHERE id = ?";
			PreparedStatement pst = c.prepareStatement(sql);
			pst.setString(1, f.getUrl().toString());
			pst.setString(2, f.getName());
			pst.setString(3, f.getFolder());
			pst.setBoolean(4, f.isApplyID3Tags());
			pst.setInt(5, f.getCheckInterval());
			pst.setString(6, f.getRenameFileTo());
			pst.setInt(7, f.getId());
			pst.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	

	public static void addElementToFeed(Feed f, FeedElement fe) {
		Connection c = null;

		try {
			c = connect();
			String sql = "INSERT INTO element " + 
			"(id_feed, name, pubdate, size, description, url, content_type, downloaded) " + 
			"VALUES ( " + 
			"?, ?, ?, ?, ?, " + 
			"?, ?, ?" + 
			")";
			
			PreparedStatement pst = c.prepareStatement(sql);
			pst.setInt(1, f.getId());
			pst.setString(2, fe.getName());
			pst.setDate(3, new java.sql.Date(fe.getDate().getTime()));
			pst.setLong(4, fe.getSize());
			pst.setString(5, fe.getDescription());
			pst.setString(6, fe.getUrl().toString());
			pst.setString(7, fe.getContenType());
			pst.setBoolean(8, fe.isDownloaded());
			pst.execute();
			
			fe.setId(lastInsertId(c));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * CALL IDENTITY();
	 * @return
	 */
	public static int lastInsertId(Connection c) {
		String sql = "CALL IDENTITY()";
		
		int n = 0;

		try {
			CallableStatement cst = c.prepareCall(sql);
			ResultSet rs = cst.executeQuery();
			if (rs.next()) {
				n = rs.getInt(1);
			} else {
				n = -1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		System.out.println("Last id: " + n);
		return n;
	}

	public static void removeElementsFromFeed(Feed feed,
		List<FeedElement> toRemove) {
		
		Connection c = null;

		try {
			c = connect();
			
			for (int i=0; i < toRemove.size(); i++) {
				FeedElement fe = toRemove.get(i);
				String sql = "DELETE FROM element WHERE id = ?";
				PreparedStatement pst = c.prepareStatement(sql);
				pst.setInt(1, fe.getId());
				pst.execute();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				closeConnection(c);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}
